package com.example.coursedesign.ui.home.dao;

import android.util.Log;

import com.example.coursedesign.ui.home.entity.Medicine;
import com.example.coursedesign.utils.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class MedicineDao {
    private static final String TAG = "mysql-coursedesign-MedicineDao";

    // 获取所有药品类别及对应的药品列表
    public Map<String, List<Medicine>> getAllMedicinesGroupedByCategory() {
        Map<String, List<Medicine>> medicinesByCategory = new HashMap<>();
        try (Connection connection = JDBCUtils.getConn()) {
            if (connection != null) {
                String sql = "SELECT * FROM medicine ORDER BY category";
                try (PreparedStatement ps = connection.prepareStatement(sql);
                     ResultSet rs = ps.executeQuery()) {
                    while (rs.next()) {
                        Integer id = rs.getInt("id");
                        String name = rs.getString("name");
                        String category = rs.getString("category");
                        String image = rs.getString("image");
                        String effect = rs.getString("effect");
                        String m_usage = rs.getString("m_usage");
                        String bad_reaction = rs.getString("bad_reaction");
                        String attention = rs.getString("attention");

                        Medicine medicine = new Medicine(id, name, category, image, effect, m_usage, bad_reaction, attention);

                        // 将药品添加到对应类别的列表中
                        medicinesByCategory.computeIfAbsent(category, k -> new ArrayList<>()).add(medicine);
                    }
                } catch (SQLException e) {
                    handleSQLException(e, "异常getAllMedicinesGroupedByCategory");
                }
            }
        } catch (SQLException e) {
            handleSQLException(e, "异常getAllMedicinesGroupedByCategory - Connection");
        }
        return medicinesByCategory;
    }

    // 获取所有类别
    public List<String> getAllCategories() {
        List<String> categories = new ArrayList<>();
        try (Connection connection = JDBCUtils.getConn()) {
            if (connection != null) {
                String sql = "SELECT DISTINCT category FROM medicine";
                try (PreparedStatement ps = connection.prepareStatement(sql);
                     ResultSet rs = ps.executeQuery()) {
                    while (rs.next()) {
                        categories.add(rs.getString("category"));
                    }
                } catch (SQLException e) {
                    handleSQLException(e, "异常getAllCategories");
                }
            }
        } catch (SQLException e) {
            handleSQLException(e, "异常getAllCategories - Connection");
        }
        return categories;
    }

    // 根据类别搜索药品
    public Map<String, List<Medicine>> searchMedicinesByCategory(String category) {
        Map<String, List<Medicine>> medicinesByCategory = new HashMap<>();
        try (Connection connection = JDBCUtils.getConn()) {
            if (connection != null) {
                String sql = "SELECT * FROM medicine WHERE category = ?";
                try (PreparedStatement ps = connection.prepareStatement(sql)) {
                    ps.setString(1, category);
                    try (ResultSet rs = ps.executeQuery()) {
                        while (rs.next()) {
                            Integer id = rs.getInt("id");
                            String name = rs.getString("name");
                            String cat = rs.getString("category");
                            String image = rs.getString("image");
                            String effect = rs.getString("effect");
                            String m_usage = rs.getString("m_usage");
                            String bad_reaction = rs.getString("bad_reaction");
                            String attention = rs.getString("attention");

                            Medicine medicine = new Medicine(id, name, cat, image, effect, m_usage, bad_reaction, attention);

                            // 将药品添加到对应类别的列表中
                            medicinesByCategory.computeIfAbsent(cat, k -> new ArrayList<>()).add(medicine);
                        }
                    }
                } catch (SQLException e) {
                    handleSQLException(e, "异常searchMedicinesByCategory");
                }
            }
        } catch (SQLException e) {
            handleSQLException(e, "异常searchMedicinesByCategory - Connection");
        }
        return medicinesByCategory;
    }

    // 根据药品名称进行模糊匹配并返回结果
    public List<Medicine> searchMedicinesByName(String name) {
        List<Medicine> medicineList = new ArrayList<>();
        try (Connection connection = JDBCUtils.getConn()) {
            if (connection != null) {
                String sql = "SELECT * FROM medicine WHERE name LIKE ?";
                try (PreparedStatement ps = connection.prepareStatement(sql)) {
                    ps.setString(1, "%" + name + "%");
                    try (ResultSet rs = ps.executeQuery()) {
                        while (rs.next()) {
                            int id = rs.getInt("id");
                            String medName = rs.getString("name");
                            String category = rs.getString("category");
                            String image = rs.getString("image");
                            String effect = rs.getString("effect");
                            String m_usage = rs.getString("m_usage");
                            String bad_reaction = rs.getString("bad_reaction");
                            String attention = rs.getString("attention");

                            Medicine medicine = new Medicine(id, medName, category, image, effect, m_usage, bad_reaction, attention);
                            medicineList.add(medicine);
                        }
                    }
                } catch (SQLException e) {
                    handleSQLException(e, "异常searchMedicinesByName");
                }
            }
        } catch (SQLException e) {
            handleSQLException(e, "异常searchMedicinesByName - Connection");
        }
        return medicineList;
    }

    // 根据类别获取科普文章（已有方法）
    public List<Medicine> getMedicinesByCategory(String category) {
        List<Medicine> medicineList = new ArrayList<>();
        try (Connection connection = JDBCUtils.getConn()) {
            if (connection != null) {
                String sql = "SELECT * FROM medicine WHERE category = ?";
                try (PreparedStatement ps = connection.prepareStatement(sql)) {
                    ps.setString(1, category);
                    try (ResultSet rs = ps.executeQuery()) {
                        while (rs.next()) {
                            int id = rs.getInt("id");
                            String name = rs.getString("name");
                            String effect = rs.getString("effect");
                            String m_usage = rs.getString("m_usage");
                            String bad_reaction = rs.getString("bad_reaction");
                            String image = rs.getString("image");
                            String attention = rs.getString("attention");

                            Medicine medicine = new Medicine(id, name, category, image, effect, m_usage, bad_reaction, attention);
                            medicineList.add(medicine);
                        }
                    }
                } catch (SQLException e) {
                    handleSQLException(e, "异常getMedicinesByCategory");
                }
            }
        } catch (SQLException e) {
            handleSQLException(e, "异常getMedicinesByCategory - Connection");
        }
        return medicineList;
    }

    // 搜索科普文章（已有方法）
    public List<Medicine> searchMedicines(String query) {
        List<Medicine> medicineList = new ArrayList<>();
        try (Connection connection = JDBCUtils.getConn()) {
            if (connection != null) {
                String sql = "SELECT * FROM medicine WHERE name LIKE ? OR category LIKE ?";
                try (PreparedStatement ps = connection.prepareStatement(sql)) {
                    ps.setString(1, "%" + query + "%");
                    ps.setString(2, "%" + query + "%");
                    try (ResultSet rs = ps.executeQuery()) {
                        while (rs.next()) {
                            int id = rs.getInt("id");
                            String name = rs.getString("name");
                            String category = rs.getString("category");
                            String effect = rs.getString("effect");
                            String m_usage = rs.getString("m_usage");
                            String bad_reaction = rs.getString("bad_reaction");
                            String image = rs.getString("image");
                            String attention = rs.getString("attention");

                            Medicine medicine = new Medicine(id, name, category, image, effect, m_usage, bad_reaction, attention);
                            medicineList.add(medicine);
                        }
                    }
                } catch (SQLException e) {
                    handleSQLException(e, "异常searchMedicines");
                }
            }
        } catch (SQLException e) {
            handleSQLException(e, "异常searchMedicines - Connection");
        }
        return medicineList;
    }

    // 获取特定ID的科普文章（已有方法）
    public Medicine getMedicineById(int id) {
        Medicine medicine = null;
        try (Connection connection = JDBCUtils.getConn()) {
            if (connection != null) {
                String sql = "SELECT * FROM medicine WHERE id = ?";
                try (PreparedStatement ps = connection.prepareStatement(sql)) {
                    ps.setInt(1, id);
                    try (ResultSet rs = ps.executeQuery()) {
                        if (rs.next()) {
                            String name = rs.getString("name");
                            String category = rs.getString("category");
                            String effect = rs.getString("effect");
                            String m_usage = rs.getString("m_usage");
                            String bad_reaction = rs.getString("bad_reaction");
                            String image = rs.getString("image");
                            String attention = rs.getString("attention");

                            medicine = new Medicine(id, name, category, image, effect, m_usage, bad_reaction, attention);
                        }
                    }
                } catch (SQLException e) {
                    handleSQLException(e, "异常getMedicineById");
                }
            }
        } catch (SQLException e) {
            handleSQLException(e, "异常getMedicineById - Connection");
        }
        return medicine;
    }

    private void handleSQLException(SQLException e, String message) {
        Log.d(TAG, message + ": " + e.getMessage());
        e.printStackTrace();
    }
}